﻿
/****** Object:  Stored Procedure dbo.usp_GetTableSizes    Script Date: 10/14/2009 10:39:06 PM ******/


create procedure usp_GetTableSizes @sortby varchar(5)='rows',@updateusage varchar(5)='False'
as
/* 
** Date: August 17, 2000
** Author: Neil Mederich
** Purpose: Report row counts and data size of user tables
** Input Parameter: @sortby = column to sort results by ('rows' or 'data' or 'name') 
** Input Parameter: @updateusage = update statistics ('true' or 'false') 
*/
set nocount on

declare @dependency char(1)
if exists(select * from sysobjects where id=object_id('sp_spaceused2') and objectproperty(id,'isprocedure')=1)
    set @dependency='T'
else
    set @dependency='F'

if @updateusage='True'
  Begin 
    DBCC UPDATEUSAGE (0) with NO_INFOMSGS 
  End

create table #sdtt ([name] nvarchar(50),[rows] int,[reserved] varchar(18),
    [data] varchar(18),[index_size] varchar(18),[unused] varchar(18))

select convert(varchar(50),a.name) as tablename into #sdt
 from sysobjects a 
 where a.type='u' and objectproperty(a.id,'istable')=1

declare @x varchar(50), @y varchar(255)
declare cOne cursor for select distinct tablename from #sdt order by tablename
open cOne
fetch next from cOne into @x
While @@fetch_status=0
  begin
    if @dependency = 'T'
      set @y = 'exec sp_spaceused2 @objname=' + @x
    else 
      set @y = 'exec sp_spaceused @objname=' + @x

    set @y = 'insert #sdtt ' + @y 
     execute(@y)
    fetch next from cOne into @x
  end
close cOne
deallocate cOne

declare @z varchar(255)
if @sortby='name'
  set @z = 'select * from #sdtt order by ' + @sortby + ' asc'
else
  set @z = 'select * from #sdtt order by ' + @sortby + ' desc'

exec (@z)



